package com.hassean.we.admin.controller;

import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.mapper.EntityWrapper;
import com.google.common.base.Strings;
import com.hassean.we.admin.common.constant.CommonConstant;
import com.hassean.we.admin.common.util.WebFileUtil;
import com.hassean.we.admin.model.entity.TbRescueTeam;
import com.hassean.we.admin.model.entity.TbSimInfo;
import com.hassean.we.admin.model.entity.TbTerminal;
import com.hassean.we.admin.service.TbRescueTeamService;
import com.hassean.we.admin.service.TbSimInfoService;
import com.hassean.we.admin.service.TbTerminalService;
import jxl.Sheet;
import jxl.Workbook;
import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.NameValuePair;
import org.apache.http.client.HttpClient;
import org.apache.http.client.entity.UrlEncodedFormEntity;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.impl.client.HttpClientBuilder;
import org.apache.http.message.BasicNameValuePair;
import org.apache.http.util.EntityUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import javax.servlet.ServletResponse;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.*;

import static java.lang.System.out;

/**
 * Created by yajun on 2019/5/19.
 */
@RestController
@RequestMapping("/file")
public class FileController {
    @Autowired private TbTerminalService tbTerminalService;
    @Autowired private TbRescueTeamService tbRescueTeamService;
    @Autowired private TbSimInfoService tbSimInfoService;
    /**
     * 下载模版
     */
    @RequestMapping("/downTemplate")
    public void downloadDate(ServletResponse response){

        WebFileUtil.downloadFile((HttpServletResponse) response, "/Template.xls" , "终端导入模板");
    }



    /**
     *   测试导入数据
     *
     * @param url
     * @return
     */
    //@RequestMapping("/testReadDate")
    public String testReadDate( @RequestParam  String url){
      return  readDate(url);
    }

    /**
     *   导入数据
     * 阅读文件
     * @param url
     * @return
     */

    public String readDate( String url){
        if (Strings.isNullOrEmpty(url)) {
            return "参数不对";
        }
        File file = new File( url);
        if(!file.exists()){
            return "未发现上传文件";
        }

        Map<String, Object> resultMap = exportEXCEData(file);
        List<String> templist = (List<String>)resultMap.get("failed");

        StringBuffer sb = new StringBuffer();
        if(templist.size()>0){

            for(String s : templist){
                sb.append(s).append("<br>");
            }
            return sb.toString();
        }

        out.println("Excel数据获取成功！正在读取数据...");
        List<Object> successList = (List<Object>) resultMap.get("success");
        for(Object map :successList){
            Map<String,Object> successMap = (Map<String, Object>) map;


            //保存数据
            saveTbTerminal(
                    successMap.get("terminalNumber").toString(),//终端号码
                    successMap.get("simNumber").toString(),//电话号码
                    successMap.get("ownerName").toString(),//终端持有人呢称
                    successMap.get("departmentName").toString(),//所属单位
                    successMap.get("simOwnerName").toString(),//实名登记人
                    Long.valueOf(successMap.get("departmentId").toString()),//救援单位id
                    Integer.valueOf(successMap.get("type").toString()),//终端类型,1是手持型，2是便携型
                    Integer.valueOf(successMap.get("state").toString()),//状态
                    successMap.get("numberAttribution").toString(),//归属地
                            successMap.get("ImsiCode").toString(),//终端IMSI
                            successMap.get("ImeiCode").toString(),//终端IMEI
                            successMap.get("provinces").toString(),//省份
                            successMap.get("municipalities").toString(),//市
                            successMap.get("districts").toString(),//区
                            successMap.get("townStreet").toString());//街道

        }
        return "成功导入"+successList.size()+"记录";
    }

    private Map<String,Object> exportEXCEData(File inputFile){

        Map<String,Object> excelMapList = new HashMap<String, Object>();
        List<String> errorList = new ArrayList<String>();
        List<Object> successList = new ArrayList<Object>();

        try {
            String fileName = inputFile.getAbsolutePath();
            String fileType = fileName.substring(fileName.lastIndexOf(".")+1,fileName.length()).toLowerCase();

            if(!"xls".equals(fileType)){
                throw new Exception("请导入2003版excel！");
            }

            Workbook book = Workbook.getWorkbook(inputFile);
            Sheet sheet = book.getSheet(0);

            if(sheet == null) {
                throw new Exception("导入模板有误！");
            }
            int fristRow = sheet.getRow(0).length;
            int rowNum = sheet.getRows();

            out.println("=================="+rowNum);
            if (rowNum <=0 || fristRow <= 0) {
                throw new Exception("导入模板有误！");
            }

            Map<String, Integer> headerMap = new HashMap<String, Integer>();
            String content = "";
            for (int i=0; i<fristRow; i++) {
                content = sheet.getCell(i, 0).getContents();
                if (!"".equals(content))
                    headerMap.put(content, i);
            }


            for (int row =1; row < rowNum; row++) {
                Map<String, Object> temMap = new HashMap<String, Object>();
                try {
                    String obj1 = "";
                    String key ="终端号码";
                    if(headerMap.containsKey(key)){
                        obj1 = sheet.getCell(headerMap.get(key), row).getContents().trim();
                    }

                    String obj2= "";
                    key ="电话号码";
                    if(headerMap.containsKey(key)){
                        obj2 = sheet.getCell(headerMap.get(key), row).getContents().trim();
                    }

                    String obj3= "";
                    key ="终端持有人呢称";
                    if(headerMap.containsKey(key)){
                        obj3 = sheet.getCell(headerMap.get(key), row).getContents().trim();
                    }

                    String obj4= "";
                    key ="所属单位";
                    if(headerMap.containsKey(key)){
                        obj4 = sheet.getCell(headerMap.get(key), row).getContents().trim();
                    }

                    String obj5= "";
                    key ="实名登记人";
                    if(headerMap.containsKey(key)){
                        obj5 = sheet.getCell(headerMap.get(key), row).getContents().trim();
                    }

                    String obj6 = "";
                    key ="终端类型";
                    if(headerMap.containsKey(key)){
                        obj6 = sheet.getCell(headerMap.get(key), row).getContents().trim();
                    }
                    String obj7 = "";
                    key ="状态";
                    if(headerMap.containsKey(key)){
                        obj7 = sheet.getCell(headerMap.get(key), row).getContents().trim();
                    }
                    String obj8= "";
                    key ="归属地";
                    if(headerMap.containsKey(key)){
                        obj8 = sheet.getCell(headerMap.get(key), row).getContents().trim();
                    }
                    String obj9= "";
                    key ="终端IMSI";
                    if(headerMap.containsKey(key)){
                        obj9 = sheet.getCell(headerMap.get(key), row).getContents().trim();
                    }
                    String obj10= "";
                    key ="终端IMEI";
                    if(headerMap.containsKey(key)){
                        obj10 = sheet.getCell(headerMap.get(key), row).getContents().trim();
                    }
                    String obj11= "";
                    key ="省份";
                    if(headerMap.containsKey(key)){
                        obj11 = sheet.getCell(headerMap.get(key), row).getContents().trim();
                    }
                    String obj12= "";
                    key ="市";
                    if(headerMap.containsKey(key)){
                        obj12 = sheet.getCell(headerMap.get(key), row).getContents().trim();
                    }
                    String obj13= "";
                    key ="区";
                    if(headerMap.containsKey(key)){
                        obj13 = sheet.getCell(headerMap.get(key), row).getContents().trim();
                    }
                    String obj14= "";
                    key ="街道";
                    if(headerMap.containsKey(key)){
                        obj14 = sheet.getCell(headerMap.get(key), row).getContents().trim();
                    }

                    if(Strings.isNullOrEmpty(obj1)){
                        errorList.add("第"+(row+1)+"行记录出错了,错误原因：终端号码为空");
                        continue;
                    }


                    EntityWrapper entity =   new EntityWrapper<>();
                    entity.eq("terminal_number",obj1.toString());
                    TbTerminal termina   = tbTerminalService.selectOne(entity);
                    if(termina!=null){
                        errorList.add("第"+(row+1)+"行记录出错了,错误原因：终端号码已经存在");
                        continue;
                    }

                    if(Strings.isNullOrEmpty(obj2)){
                        errorList.add("第"+(row+1)+"行记录出错了,错误原因：电话号码为空");
                        continue;
                    }else{
                        if(obj2.toString().length() != 11){
                            errorList.add("第"+(row+1)+"行记录出错了,错误原因：电话格式不对，应为11位");
                            continue;
                        }
                    }
                    //电话号码判重
                    EntityWrapper entityWrapper =   new EntityWrapper<>();
                    entityWrapper.eq("sim_number",obj2.toString());
                    TbSimInfo tbSimInfo = tbSimInfoService.selectOne(entityWrapper);

                    if(tbSimInfo!=null){
                        errorList.add("第"+(row+1)+"行记录出错了,错误原因：电话号码已经存在");
                        continue;
                    }
                    if(Strings.isNullOrEmpty(obj3)){
                        errorList.add("第"+(row+1)+"行记录出错了,错误原因：终端持有人呢称为空");
                        continue;
                    }
                    if(Strings.isNullOrEmpty(obj4)){
                        errorList.add("第"+(row+1)+"行记录出错了,错误原因：所属单位为空");
                        continue;
                    }

                    Long  departmentId = -1L;//用于测试

                    try{
                        departmentId= getDeptIdByName(obj4);

                    }catch (Exception e){
                        e.printStackTrace();
                    }
                    if(departmentId==0L){
                        errorList.add("第"+(row+1)+"行记录出错了,错误原因：所属单位不存在");
                        continue;
                    }

                    if(Strings.isNullOrEmpty(obj5)){
                        errorList.add("第"+(row+1)+"行记录出错了,错误原因：实名登记人为空");
                        continue;
                    }
                    if(Strings.isNullOrEmpty(obj6)){
                        errorList.add("第"+(row+1)+"行记录出错了,错误原因：终端类型为空");
                        continue;
                    }


                    if(errorList.size() > 0){
                        continue ;
                    }


                    temMap.put("departmentId", departmentId);//救援单位id,不用写
                    temMap.put("terminalNumber", obj1);//终端号码
                    temMap.put("simNumber", obj2);//电话号码
                    temMap.put("ownerName", obj3);//终端持有人呢称
                    temMap.put("departmentName", obj4);//所属单位
                    temMap.put("simOwnerName", obj5);//实名登记人
                    temMap.put("type", obj6);//终端类型,1是手持型，2是便携型
                    temMap.put("state", obj7);//状态
                    temMap.put("numberAttribution", obj8);//归属地
                    temMap.put("ImsiCode", obj9);//终端IMSI
                    temMap.put("ImeiCode", obj10);//终端IMEI
                    temMap.put("provinces", obj11);//省份
                    temMap.put("municipalities", obj12);//市
                    temMap.put("districts", obj13);//区
                    temMap.put("townStreet", obj14);//街道

                    successList.add(temMap);

                } catch (Exception e) {
                    e.printStackTrace();
                    errorList.add("第"+(row+1)+"行记录出错了,错误原因：未知错误！");
                    continue ;
                }
            }
            excelMapList.put("failed", errorList);
            excelMapList.put("success", successList);
        } catch (Exception e) { e.printStackTrace(); }

        return excelMapList;
    }


    //添加数据
    public void saveTbTerminal(String terminalNumber, String simNumber,String ownerName,
                                                String departmentName,  String simOwnerName,
                                                Long departmentId, Integer type, Integer state,String numberAttribution,
                                                String ImsiCode,  String ImeiCode,  String provinces,  String municipalities,
                                                String districts,  String townStreet) {
        Boolean flag = false;
        TbTerminal tbTerminal = new TbTerminal();
        TbSimInfo tbSimInfo = new TbSimInfo();
        tbSimInfo.setOwnerName(simOwnerName);//实名登记的人
        tbSimInfo.setSimNumber(simNumber);
        tbSimInfo.setDepartmentName(departmentName);//所属单位
        tbSimInfoService.insertAndReturnId(tbSimInfo);
        Long id = tbSimInfo.getSimInfoId();


        TbRescueTeam tbRescueTeam = new TbRescueTeam();
        tbRescueTeam.setDepartmentName(departmentName);
        tbRescueTeam.setDistricts(districts);
        tbRescueTeam.setTownStreet(townStreet);
        tbRescueTeam.setProvinces(provinces);
        tbRescueTeam.setMunicipalities(municipalities);
        tbRescueTeam.setDepartmentId(departmentId);
        tbRescueTeamService.insertAndReturnId(tbRescueTeam);
        Long rescueTeamId =  tbRescueTeam.getRescueTeamId();


        tbTerminal.setSimInfoId(id);
        tbTerminal.setTerminalNumber(terminalNumber);
        tbTerminal.setImeiCode(ImeiCode);
        tbTerminal.setImsiCode(ImsiCode);
        tbTerminal.setState(state);
        tbTerminal.setType(type);
        tbTerminal.setRescueTeamId(rescueTeamId);//要通过救援单位名称查出来
        tbTerminal.setNumberAttribution(numberAttribution);
        tbTerminal.setOwnerName(ownerName);
        tbTerminalService.insert(tbTerminal);



    }

    //修改数据
    public void editTbTerminal(String terminalNumber, String simNumber,String ownerName,
                               String departmentName,  String simOwnerName,
                               Long departmentId, Integer type, Integer state,String numberAttribution,
                               String ImsiCode,  String ImeiCode,  String provinces,  String municipalities,
                               String districts,  String townStreet) {
        Boolean flag = false;

        EntityWrapper entity_terminal_number = new EntityWrapper<>();
        entity_terminal_number.eq("terminal_number",terminalNumber);
        TbTerminal tbTerminal = tbTerminalService.selectOne(entity_terminal_number);



        EntityWrapper entity_simNumber= new EntityWrapper<>();
        entity_simNumber.eq("sim_number",simNumber);

        TbSimInfo tbSimInfo = tbSimInfoService.selectOne(entity_simNumber);
        tbSimInfo.setOwnerName(simOwnerName);//实名登记的人
        tbSimInfo.setSimNumber(simNumber);
        tbSimInfo.setDepartmentName(departmentName);//所属单位
         tbSimInfoService.updateById(tbSimInfo);


        EntityWrapper entity_department_id= new EntityWrapper<>();
        entity_department_id.eq("department_id",departmentId);

        TbRescueTeam tbRescueTeam = tbRescueTeamService.selectOne(entity_department_id);

        tbRescueTeam.setDepartmentName(departmentName);
        tbRescueTeam.setDistricts(districts);
        tbRescueTeam.setTownStreet(townStreet);
        tbRescueTeam.setProvinces(provinces);
        tbRescueTeam.setMunicipalities(municipalities);
        tbRescueTeam.setDepartmentId(departmentId);
        tbRescueTeamService.updateById(tbRescueTeam);
        tbRescueTeam.getRescueTeamId();



        tbTerminal.setTerminalNumber(terminalNumber);
        tbTerminal.setImeiCode(ImeiCode);
        tbTerminal.setImsiCode(ImsiCode);
        tbTerminal.setState(state);
        tbTerminal.setType(type);

        tbTerminal.setNumberAttribution(numberAttribution);
        tbTerminal.setOwnerName(ownerName);
        tbTerminalService.updateById(tbTerminal);



    }


    //通过救援单位名称，获取救援单位id
    public Long getDeptIdByName(String name) {
        HttpClient client = HttpClientBuilder.create().build();
        HttpPost post = new HttpPost(CommonConstant.passAuthUrl+"/admin/dept/deptByName");
        List<NameValuePair> params=new ArrayList<NameValuePair>();
        params.add(new BasicNameValuePair("name",name));
        Long deptId = 0L;
        //添加参数
        try {
            post.setEntity(new UrlEncodedFormEntity(params, "utf-8"));
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        try {
            HttpResponse response =  client.execute(post);
            HttpEntity entity = response.getEntity();
            String result = EntityUtils.toString(entity, "utf-8");
            out.println(result);


            Map<String ,Object> map = JSON.parseObject(result, Map.class);
            //System.out.println(" map.get(\"username\"):"+ map.get("user_name"));
            Integer code = Integer.parseInt(map.get("code").toString()) ;
            if(code==0) {
                Map<String, Object> data = new HashMap<>();
                data = (Map<String, Object>) map.get("data");
                deptId = Long.parseLong(data.get("deptId").toString());
            }

        } catch (IOException e) {
            e.printStackTrace();
        }
        return   deptId;
    }

    //上传文件
    @RequestMapping(value = "/upload/data")
    public String  importExcel(HttpServletRequest request )throws IOException {
        String  magesse = "上传失败";
        MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;
            // 获得文件
        MultipartFile file = multiRequest.getFile("file");
            if (!file.isEmpty()) {
                //用来检测程序运行时间
                long  startTime=System.currentTimeMillis();
                try {
                    //获取输出流
                    String filename = file.getOriginalFilename();
                    out.println("fileName："+file.getOriginalFilename());
                    String url = "F:/"+new Date().getTime()+file.getOriginalFilename();//写入的地址
                    //url= "http://47.105.159.199/temp/"+new Date().getTime()+file.getOriginalFilename();
                    out.println("url:"+url);
                    OutputStream os=new FileOutputStream(url);
                    //获取输入流 CommonsMultipartFile 中可以直接得到文件的流
                    InputStream is=file.getInputStream();
                    byte[] bts = new byte[1024];
                    //一个一个字节的读取并写入
                    while(is.read(bts)!=-1)
                    {
                        os.write(bts);
                    }
                    magesse =readDate(url);
                    os.flush();
                    os.close();
                    is.close();

                } catch (FileNotFoundException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }

        return magesse;
    }

    //修改文件
    @RequestMapping(value = "/edit/data")
    public String  editExcel(HttpServletRequest request )throws IOException {
        String  magesse = "上传失败";
        MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;
        // 获得文件
        MultipartFile file = multiRequest.getFile("file");
        if (!file.isEmpty()) {
            //用来检测程序运行时间
            long  startTime=System.currentTimeMillis();
            try {
                //获取输出流
                String filename = file.getOriginalFilename();
                out.println("fileName："+file.getOriginalFilename());
                String url = "F:/"+new Date().getTime()+file.getOriginalFilename();//写入的地址
                //url= "http://47.105.159.199/temp/"+new Date().getTime()+file.getOriginalFilename();
                out.println("url:"+url);
                OutputStream os=new FileOutputStream(url);
                //获取输入流 CommonsMultipartFile 中可以直接得到文件的流
                InputStream is=file.getInputStream();
                byte[] bts = new byte[1024];
                //一个一个字节的读取并写入
                while(is.read(bts)!=-1)
                {
                    os.write(bts);
                }
                magesse =editDate(url);
                os.flush();
                os.close();
                is.close();

            } catch (FileNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }

        return magesse;
    }

    public String editDate( String url){
        if (Strings.isNullOrEmpty(url)) {
            return "参数不对";
        }
        File file = new File( url);
        if(!file.exists()){
            return "未发现上传文件";
        }

        Map<String, Object> resultMap = editEXCEData(file);
        List<String> templist = (List<String>)resultMap.get("failed");

        StringBuffer sb = new StringBuffer();
        if(templist.size()>0){

            for(String s : templist){
                sb.append(s).append("<br>");
            }
            return sb.toString();
        }

        out.println("Excel数据获取成功！正在读取数据...");
        List<Object> successList = (List<Object>) resultMap.get("success");
        for(Object map :successList){
            Map<String,Object> successMap = (Map<String, Object>) map;


            //保存数据
            editTbTerminal(
                    successMap.get("terminalNumber").toString(),//终端号码
                    successMap.get("simNumber").toString(),//电话号码
                    successMap.get("ownerName").toString(),//终端持有人呢称
                    successMap.get("departmentName").toString(),//所属单位
                    successMap.get("simOwnerName").toString(),//实名登记人
                    Long.valueOf(successMap.get("departmentId").toString()),//救援单位id
                    Integer.valueOf(successMap.get("type").toString()),//终端类型,1是手持型，2是便携型
                    Integer.valueOf(successMap.get("state").toString()),//状态
                    successMap.get("numberAttribution").toString(),//归属地
                    successMap.get("ImsiCode").toString(),//终端IMSI
                    successMap.get("ImeiCode").toString(),//终端IMEI
                    successMap.get("provinces").toString(),//省份
                    successMap.get("municipalities").toString(),//市
                    successMap.get("districts").toString(),//区
                    successMap.get("townStreet").toString());//街道

        }
        return "成功导入"+successList.size()+"记录";
    }

    private Map<String,Object> editEXCEData(File inputFile){

        Map<String,Object> excelMapList = new HashMap<String, Object>();
        List<String> errorList = new ArrayList<String>();
        List<Object> successList = new ArrayList<Object>();

        try {
            String fileName = inputFile.getAbsolutePath();
            String fileType = fileName.substring(fileName.lastIndexOf(".")+1,fileName.length()).toLowerCase();

            if(!"xls".equals(fileType)){
                throw new Exception("请导入2003版excel！");
            }

            Workbook book = Workbook.getWorkbook(inputFile);
            Sheet sheet = book.getSheet(0);

            if(sheet == null) {
                throw new Exception("导入模板有误！");
            }
            int fristRow = sheet.getRow(0).length;
            int rowNum = sheet.getRows();

            out.println("=================="+rowNum);
            if (rowNum <=0 || fristRow <= 0) {
                throw new Exception("导入模板有误！");
            }

            Map<String, Integer> headerMap = new HashMap<String, Integer>();
            String content = "";
            for (int i=0; i<fristRow; i++) {
                content = sheet.getCell(i, 0).getContents();
                if (!"".equals(content))
                    headerMap.put(content, i);
            }


            for (int row =1; row < rowNum; row++) {
                Map<String, Object> temMap = new HashMap<String, Object>();
                try {
                    String obj1 = "";
                    String key ="终端号码";
                    if(headerMap.containsKey(key)){
                        obj1 = sheet.getCell(headerMap.get(key), row).getContents().trim();
                    }

                    String obj2= "";
                    key ="电话号码";
                    if(headerMap.containsKey(key)){
                        obj2 = sheet.getCell(headerMap.get(key), row).getContents().trim();
                    }

                    String obj3= "";
                    key ="终端持有人呢称";
                    if(headerMap.containsKey(key)){
                        obj3 = sheet.getCell(headerMap.get(key), row).getContents().trim();
                    }

                    String obj4= "";
                    key ="所属单位";
                    if(headerMap.containsKey(key)){
                        obj4 = sheet.getCell(headerMap.get(key), row).getContents().trim();
                    }

                    String obj5= "";
                    key ="实名登记人";
                    if(headerMap.containsKey(key)){
                        obj5 = sheet.getCell(headerMap.get(key), row).getContents().trim();
                    }

                    String obj6 = "";
                    key ="终端类型";
                    if(headerMap.containsKey(key)){
                        obj6 = sheet.getCell(headerMap.get(key), row).getContents().trim();
                    }
                    String obj7 = "";
                    key ="状态";
                    if(headerMap.containsKey(key)){
                        obj7 = sheet.getCell(headerMap.get(key), row).getContents().trim();
                    }
                    String obj8= "";
                    key ="归属地";
                    if(headerMap.containsKey(key)){
                        obj8 = sheet.getCell(headerMap.get(key), row).getContents().trim();
                    }
                    String obj9= "";
                    key ="终端IMSI";
                    if(headerMap.containsKey(key)){
                        obj9 = sheet.getCell(headerMap.get(key), row).getContents().trim();
                    }
                    String obj10= "";
                    key ="终端IMEI";
                    if(headerMap.containsKey(key)){
                        obj10 = sheet.getCell(headerMap.get(key), row).getContents().trim();
                    }
                    String obj11= "";
                    key ="省份";
                    if(headerMap.containsKey(key)){
                        obj11 = sheet.getCell(headerMap.get(key), row).getContents().trim();
                    }
                    String obj12= "";
                    key ="市";
                    if(headerMap.containsKey(key)){
                        obj12 = sheet.getCell(headerMap.get(key), row).getContents().trim();
                    }
                    String obj13= "";
                    key ="区";
                    if(headerMap.containsKey(key)){
                        obj13 = sheet.getCell(headerMap.get(key), row).getContents().trim();
                    }
                    String obj14= "";
                    key ="街道";
                    if(headerMap.containsKey(key)){
                        obj14 = sheet.getCell(headerMap.get(key), row).getContents().trim();
                    }

                    if(Strings.isNullOrEmpty(obj1)){
                        errorList.add("第"+(row+1)+"行记录出错了,错误原因：终端号码为空");
                        continue;
                    }


                    EntityWrapper entity =   new EntityWrapper<>();
                    entity.eq("terminal_number",obj1.toString());
                    //通过省市区，用户权限搜索，必须是属于自己的权限内的终端号码才能修改
                    TbTerminal termina   = tbTerminalService.selectOne(entity);
                    if(termina==null){
                        errorList.add("第"+(row+1)+"行记录出错了,错误原因：终端号码已经不存在");
                        continue;
                    }

                    if(Strings.isNullOrEmpty(obj2)){
                        errorList.add("第"+(row+1)+"行记录出错了,错误原因：电话号码为空");
                        continue;
                    }else{
                        if(obj2.toString().length() != 11){
                            errorList.add("第"+(row+1)+"行记录出错了,错误原因：电话格式不对，应为11位");
                            continue;
                        }
                    }
                    //电话号码判重
                    //通过省市区，用户权限搜索，必须是属于自己的权限内的终端号码才能修改
                    EntityWrapper entityWrapper =   new EntityWrapper<>();
                    entityWrapper.eq("sim_number",obj2.toString());
                    TbSimInfo tbSimInfo = tbSimInfoService.selectOne(entityWrapper);

                    if(tbSimInfo==null){
                        errorList.add("第"+(row+1)+"行记录出错了,错误原因：电话号码不存在");
                        continue;
                    }
                    if(Strings.isNullOrEmpty(obj3)){
                        errorList.add("第"+(row+1)+"行记录出错了,错误原因：终端持有人呢称为空");
                        continue;
                    }
                    if(Strings.isNullOrEmpty(obj4)){
                        errorList.add("第"+(row+1)+"行记录出错了,错误原因：所属单位为空");
                        continue;
                    }

                    Long  departmentId = -1L;//用于测试

                    try{
                        departmentId= getDeptIdByName(obj4);

                    }catch (Exception e){
                        e.printStackTrace();
                    }
                    if(departmentId==0L){
                        errorList.add("第"+(row+1)+"行记录出错了,错误原因：所属单位不存在");
                        continue;
                    }

                    if(Strings.isNullOrEmpty(obj5)){
                        errorList.add("第"+(row+1)+"行记录出错了,错误原因：实名登记人为空");
                        continue;
                    }
                    if(Strings.isNullOrEmpty(obj6)){
                        errorList.add("第"+(row+1)+"行记录出错了,错误原因：终端类型为空");
                        continue;
                    }


                    if(errorList.size() > 0){
                        continue ;
                    }


                    temMap.put("departmentId", departmentId);//救援单位id,不用写
                    temMap.put("terminalNumber", obj1);//终端号码
                    temMap.put("simNumber", obj2);//电话号码
                    temMap.put("ownerName", obj3);//终端持有人呢称
                    temMap.put("departmentName", obj4);//所属单位
                    temMap.put("simOwnerName", obj5);//实名登记人
                    temMap.put("type", obj6);//终端类型,1是手持型，2是便携型
                    temMap.put("state", obj7);//状态
                    temMap.put("numberAttribution", obj8);//归属地
                    temMap.put("ImsiCode", obj9);//终端IMSI
                    temMap.put("ImeiCode", obj10);//终端IMEI
                    temMap.put("provinces", obj11);//省份
                    temMap.put("municipalities", obj12);//市
                    temMap.put("districts", obj13);//区
                    temMap.put("townStreet", obj14);//街道

                    successList.add(temMap);

                } catch (Exception e) {
                    e.printStackTrace();
                    errorList.add("第"+(row+1)+"行记录出错了,错误原因：未知错误！");
                    continue ;
                }
            }
            excelMapList.put("failed", errorList);
            excelMapList.put("success", successList);
        } catch (Exception e) { e.printStackTrace(); }

        return excelMapList;
    }


    //导出报表
    @RequestMapping(value = "/download/data")
    public  Map<String,Object> download(@RequestParam Map<String, Object> params) {
        Map<String,Object>resultMap = new HashMap<>();
        //第一步，创建一个workbook对应一个excel文件
        HSSFWorkbook workbook = new HSSFWorkbook();
        //第二部，在workbook中创建一个sheet对应excel中的sheet
        HSSFSheet sheet = workbook.createSheet("表一");
        //第三部，在sheet表中添加表头第0行，老版本的poi对sheet的行列有限制
        HSSFRow row = sheet.createRow(0);
        //第四步，创建单元格，设置表头
        HSSFCell cell = row.createCell(0);
        cell.setCellValue("终端号码");
        cell = row.createCell(1);
        cell.setCellValue("电话号码");
        cell = row.createCell(2);
        cell.setCellValue("终端持有人呢称");
        cell = row.createCell(3);
        cell.setCellValue("所属单位");
        cell = row.createCell(4);
        cell.setCellValue("实名登记人");
        cell = row.createCell(5);
        cell.setCellValue("终端类型");
        cell = row.createCell(6);
        cell.setCellValue("状态");
        cell = row.createCell(7);
        cell.setCellValue("归属地");
        cell = row.createCell(8);
        cell.setCellValue("终端IMSI");
        cell = row.createCell(9);
        cell.setCellValue("终端IMEI");
        cell = row.createCell(10);
        cell.setCellValue("省份");
        cell = row.createCell(11);
        cell.setCellValue("市");
        cell = row.createCell(12);
        cell.setCellValue("区");
        cell = row.createCell(13);
        cell.setCellValue("街道");

         Map<String, Object> map= getTbTerminalList(params);
        Integer total = Integer.parseInt(map.get("total").toString());
        List< Map<String, Object>>list = (List)map.get("resultList");


        //第五步，写入实体数据，实际应用中这些数据从数据库得到,对象封装数据，集合包对象。对象的属性值对应表的每行的值
        for (int i = 0; i < total; i++) {
            HSSFRow row1 = sheet.createRow(i + 1);
            //创建单元格设值


            String terminalNumber = list.get(i).get("terminalNumber").toString();

            String simNumber = list.get(i).get("simNumber").toString();

            String ownerName = list.get(i).get("ownerName").toString();

            String departmentName = list.get(i).get("departmentName").toString();

            String simOwnerName = list.get(i).get("simOwnerName").toString();

            String type = list.get(i).get("type").toString();

            String state = list.get(i).get("state").toString();

            String numberAttribution = list.get(i).get("numberAttribution").toString();

            String imsiCode = list.get(i).get("imsiCode").toString();

            String imeiCode = list.get(i).get("imeiCode").toString();

            String provinces = list.get(i).get("provinces").toString();

            String municipalities = list.get(i).get("municipalities").toString();

            String districts = list.get(i).get("districts").toString();

            String townStreet = list.get(i).get("townStreet").toString();



            row1.createCell(0).setCellValue(terminalNumber);//终端号码
            row1.createCell(1).setCellValue(simNumber);//电话号码
            row1.createCell(2).setCellValue(ownerName);//终端持有人呢称
            row1.createCell(3).setCellValue(departmentName);//所属单位
            row1.createCell(4).setCellValue(simOwnerName);//实名登记人
            row1.createCell(5).setCellValue(type);//终端类型
            row1.createCell(6).setCellValue(state);//状态
            row1.createCell(7).setCellValue(numberAttribution);//归属地
            row1.createCell(8).setCellValue(imsiCode);//终端IMSI
            row1.createCell(9).setCellValue(imeiCode);//终端IMEI
            row1.createCell(10).setCellValue(provinces);//省份
            row1.createCell(11).setCellValue(municipalities);//市
            row1.createCell(12).setCellValue(districts);//区
            row1.createCell(13).setCellValue(townStreet);//街道

        }
        String url = null;
        //将文件保存到指定的位置
        try {
            url ="F:/"+new Date().getTime()+"报表.xls";
            FileOutputStream fos = new FileOutputStream(url);
            workbook.write(fos);
            System.out.println("写入成功");
            resultMap.put("url",url);
            resultMap.put("code",CommonConstant.success);
            fos.close();
        } catch (IOException e) {
            resultMap.put("url",null);
            resultMap.put("code",CommonConstant.failed);
            e.printStackTrace();
        }
        return   resultMap;
    }


    public Map<String, Object> getTbTerminalList(Map<String, Object> params) {
        EntityWrapper entity = new EntityWrapper<>();
        entity.eq(CommonConstant.DEL_FLAG,CommonConstant.STATUS_NORMAL);
        List< Map<String, Object>>resultList = new ArrayList<>();
        Map<String,Object> resultMap = new HashMap<>();
        //类型，在线状态，部门
        if (params.get("state")!=null&&!(params.get("state")).equals("")){
            Integer state = Integer.parseInt(params.get("state").toString());
            entity.eq("state",state);//在线状态条件查询
        }
        if (params.get("terminal_number")!=null&&!(params.get("terminal_number")).equals("")){
            String terminal_number = params.get("terminal_number").toString();
            entity.eq("terminal_number",terminal_number);//在线状态条件查询
        }
        if (params.get("type")!=null&&!(params.get("type")).equals("")){

            Integer type = Integer.parseInt(params.get("type").toString());
            entity.eq("type",type);//类型条件查询
        }
        String phone = null;
        if (params.get("phone")!=null&&!(params.get("phone")).equals("")){
            phone=params.get("phone").toString(); //电话号码条件查询

        }
        //通过电话号码获取终端id
        List<Map<String, Object>> listmap = tbTerminalService.selectTerminalIdByPhone(phone);
        //获取被叫终端id
        if(!listmap.isEmpty()){
            Long terminal_id  = Long.parseLong(listmap.get(0).get("terminal_id").toString());
            entity.eq("terminal_id",terminal_id);
        }

        Long departmentId = null;
        if (params.get("departmentId")!=null&&!(params.get("departmentId")).equals("")){
            departmentId = Long.parseLong(params.get("departmentId").toString());
        }


        List<TbTerminal> list = tbTerminalService.selectList( entity);
        if(!list.isEmpty()){
            for (int i = 0; i <list.size() ; i++) {

                Map<String,Object>map = new HashMap<>();
                String  terminalNumber =  list.get(i).getTerminalNumber();
                terminalNumber =  (terminalNumber == null ? "" : terminalNumber);
                map.put("terminalNumber",terminalNumber);//终端号码
                String  ownerName =  list.get(i).getOwnerName();
                ownerName =  (ownerName == null ? "" : ownerName);
                map.put("ownerName",ownerName); //终端持有人呢称
                String  type =  list.get(i).getType().toString();
                type =  (type == null ? "" : type);
                if( type.equals("1")){  //终端类型
                    map.put("type","手持型");
                }else if( type.equals("2")){
                    map.put("type","便携型");
                }else{
                    map.put("type","其他");
                }
                String  state =  list.get(i).getState().toString();
                state =  (state == null ? "" : state);
                if(state.equals("1")){   //状态
                    map.put("state","在线");
                }
                if(state.equals("2")){
                    map.put("state","离线");
                }else{
                    map.put("state","其他");
                }

                String  numberAttribution =  list.get(i).getNumberAttribution();
                numberAttribution =  (numberAttribution == null ? "" : numberAttribution);
                map.put("numberAttribution",numberAttribution);//归属地
                String imsiCode =list.get(i).getImsiCode();
                imsiCode =  (imsiCode == null ? "" : imsiCode);
                map.put("imsiCode",imsiCode); //终端IMSI
                String imeiCode =list.get(i).getImeiCode();
                imeiCode =  (imeiCode == null ? "" : imeiCode);
                map.put("imeiCode",imeiCode); //终端IMEI

                Long rescueTeamId =list.get(i).getRescueTeamId();//获取所属单位id
                Long  simInfoId =  list.get(i).getSimInfoId();//获取sim卡id
                if(rescueTeamId!=null){
                    TbRescueTeam tbRescueTeam =  tbRescueTeamService.selectById(rescueTeamId);
                    if(tbRescueTeam!=null){
                        String departmentName = tbRescueTeam.getDepartmentName();
                        departmentName =  (departmentName == null ? "" : departmentName);
                        String provinces = tbRescueTeam.getProvinces();
                        provinces =  (provinces == null ? "" : provinces);
                        String municipalities = tbRescueTeam.getMunicipalities();
                        municipalities =  (municipalities == null ? "" : municipalities);
                        String districts = tbRescueTeam.getDistricts();
                        districts =  (districts == null ? "" : districts);
                        String townStreet = tbRescueTeam.getTownStreet();
                        townStreet =  (townStreet == null ? "" : townStreet);
                        //如果时所属单位条件查询
                        if(departmentId!=null){
                            if(tbRescueTeam.getDepartmentId()==departmentId){
                                map.put("departmentName",departmentName); //所属单位
                                map.put("provinces",provinces);  //省份
                                map.put("municipalities",municipalities); //市
                                map.put("districts",districts);   //区
                                map.put("townStreet",townStreet);    //街道
                            }else{
                                continue;
                            }
                        }else{
                            map.put("departmentName",departmentName); //所属单位
                            map.put("provinces",provinces);  //省份
                            map.put("municipalities",municipalities); //市
                            map.put("districts",districts);   //区
                            map.put("townStreet",townStreet);    //街道
                        }


                    }
                }
                if(simInfoId!=null){
                    TbSimInfo tbSimInfo = tbSimInfoService.selectById(simInfoId);
                    if(tbSimInfo!=null){
                        String simNumber = tbSimInfo.getSimNumber();
                        simNumber =  (simNumber == null ? "" : simNumber);
                        map.put("simNumber",simNumber);//电话号码
                        String simOwnerName = tbSimInfo.getOwnerName();
                        simOwnerName =  (simOwnerName == null ? "" : simOwnerName);
                        map.put("simOwnerName",simOwnerName);//实名登记人
                    }
                }

                resultList.add(map);
            }
        }


        resultMap.put("total",list.size());

        resultMap.put("resultList",resultList);

        return resultMap;

    }


    }
